/*

	EXEC sp_cycle_time_yield_report_by_process_type 'Assembly','0'
*/

ALTER PROC sp_cycle_time_yield_report_by_process_type

@PROCESS_TYPE	VARCHAR(50),
@DEFAULT_PROMPT	VARCHAR(20)

AS
BEGIN
	-- RESULT TABLE TO STORE FINAL RESULTS TO BE SUPPLIED FOR THE GRAPH
	DECLARE @FINAL_RESULT_TABLE TABLE (YIELD		FLOAT,
					   CYTCLETIME_SNO	INT,				
					   CYCLETIME_VAR	VARCHAR(20),
					   QTY_IN		INT,
					   QTY_OUT		INT,
					   START_DATE		DATETIME,
					   END_DATE		DATETIME)

	-- INTERMEDIATE TABLE TO STORE LOT INFO TO GET YIELD AND CALCULATE CYCLE TIMES FOR A PROCESS 
	DECLARE @RESULT_TABLE TABLE (   PART_NUMBER 		VARCHAR(50), 
					PROCESSSTEP_ID 		BIGINT,
					LOT_NUMBER 		VARCHAR(50),
					DATE_IN 		DATETIME,
					DATE_OUT		DATETIME,
					QTY_IN			INT,
					QTY_OUT			INT,
					GROSS_DIE		INT,
					YIELD			FLOAT,
					LOCATION		VARCHAR(50),
					PROCESS			VARCHAR(20),
					PROCESS_TYPE_ID 	INT,
					FAB_LOT_NUMBER		VARCHAR(50),
					EXPECTED_DATE_OUT 	DATETIME,
					ACTUAL_CYCLE_TIME	INT,
					PROJECTED_CYCLE_TIME	INT,
					COMMITED_CYCLE_TIME	INT)

	--DECLARE @WORK_WEEK_TABLE TABLE ( FROM_DATE DATETIME, TO_DATE DATETIME )

	DECLARE @STARTDATE		DATETIME
	DECLARE @ENDDATE 		DATETIME
	DECLARE @TO_DATE		DATETIME
	DECLARE @MONTH_STARTDATE	DATETIME
	DECLARE @MONTH_ENDDATE		DATETIME
	DECLARE @CYCLETIME_STRING	NVARCHAR(100)
	DECLARE @AUTO_NO 		INT
	DECLARE @YIELD			FLOAT
	DECLARE @QTY_IN			INT
	DECLARE @QTY_OUT		INT

	INSERT INTO @RESULT_TABLE ( PART_NUMBER,PROCESSSTEP_ID,LOT_NUMBER ,DATE_IN ,DATE_OUT,QTY_IN,QTY_OUT,GROSS_DIE,YIELD,LOCATION,PROCESS,PROCESS_TYPE_ID,FAB_LOT_NUMBER) 
	SELECT Part_Number, ProcessStep_Id, Lot_Number, CONVERT( VARCHAR, Date_In, 101 ), CONVERT( VARCHAR, Date_Out, 101 ), Qty_In, Qty_Out, ISNULL( Gross_Die, 1 ), ROUND( Yield, 0 ), Location, Process, Process_Type_Id, Fab_Lot_Number
	FROM FN_MATRIX_YIELD_REPORT ( 0, @PROCESS_TYPE , '0', '0', '0')


	--SELECT * FROM @RESULT_TABLE

	-- FOR LAST THREE QUARTERS INFORMATION 
	DECLARE @CYTCLETIME_SNO INT
	DECLARE @QTR_START_DATE DATETIME
	DECLARE @QTR_END_DATE DATETIME


	SET @QTR_START_DATE = DATEADD(QQ, DATEDIFF(QQ,0,GETDATE()), 0)-- IT RETURNS START DATE OF PRESENT QTR

	SET @QTR_START_DATE = DATEADD( Q, -2, @QTR_START_DATE )+' 12:00AM'-- IT RETURNS START DATE OF PREVIOUS TWO QTR'S

	SET @CYTCLETIME_SNO = 1
	WHILE ( @CYTCLETIME_SNO <= 3 )
	BEGIN

		SET @QTR_END_DATE =  DATEADD( D, -1, DATEADD( Q, 1, @QTR_START_DATE ) )+' 11:59PM' 
		SET @CYCLETIME_STRING = N'QTR ' + CONVERT( NVARCHAR , (4-@CYTCLETIME_SNO) )
		IF ( @PROCESS_TYPE = 'SORT' )
		BEGIN
			SELECT @YIELD = ISNULL(  ( SUM( QTY_OUT ) * 100 / SUM( QTY_IN * GROSS_DIE ) ) ,0.0 ),@QTY_OUT = ISNULL( SUM( QTY_OUT ), 0),@QTY_IN = ISNULL( SUM( QTY_IN * GROSS_DIE ), 0)
			FROM @RESULT_TABLE WHERE DATE_IN >= @QTR_START_DATE AND DATE_OUT <= @QTR_END_DATE
		END
		ELSE
		BEGIN
			SELECT @YIELD = ISNULL(  ( SUM( QTY_OUT ) * 100 / SUM( QTY_IN  )) ,0.0 ),@QTY_OUT = ISNULL( SUM( QTY_OUT ), 0),@QTY_IN = ISNULL( SUM( QTY_IN ), 0)
			FROM @RESULT_TABLE WHERE DATE_IN >= @QTR_START_DATE AND DATE_OUT <= @QTR_END_DATE
		END
		INSERT INTO @FINAL_RESULT_TABLE ( YIELD,CYTCLETIME_SNO,CYCLETIME_VAR,QTY_IN,QTY_OUT, START_DATE, END_DATE ) VALUES ( @YIELD,@CYTCLETIME_SNO,@CYCLETIME_STRING,@QTY_IN,@QTY_OUT, @QTR_START_DATE, @QTR_END_DATE )

		SET @QTR_START_DATE =  DATEADD( Q, 1, @QTR_START_DATE )+' 12:00AM' -- IT RETURNS THE START DATE OF THE PREVIOUS

		SET @CYTCLETIME_SNO = @CYTCLETIME_SNO + 1
		SET @YIELD = NULL
	END

	-- FOR CURRENT MONTH INFORMATION
	
	SET @STARTDATE = DATEADD(MM, DATEDIFF(MM,0,GETDATE()), 0) +' 12:00AM' -- RETURNS MONTH START DATE
	SET @ENDDATE =DATEADD(MS,-3,DATEADD(MM, DATEDIFF(M,0,GETDATE()  )+1, 0)) -- RETURNS MONTH END DATE

	SET @CYCLETIME_STRING = 
				CASE 
					WHEN DATEPART(MM, @STARTDATE ) = 1 THEN 'JANUARY'
					WHEN DATEPART(MM, @STARTDATE ) = 2 THEN 'FEBRUARY'
					WHEN DATEPART(MM, @STARTDATE ) = 3 THEN 'MARCH'
					WHEN DATEPART(MM, @STARTDATE ) = 4 THEN 'APRIL'
					WHEN DATEPART(MM, @STARTDATE ) = 5 THEN 'MAY'
					WHEN DATEPART(MM, @STARTDATE ) = 6 THEN 'JUNE'
					WHEN DATEPART(MM, @STARTDATE ) = 7 THEN 'JULY'
					WHEN DATEPART(MM, @STARTDATE ) = 8 THEN 'AUGUST'
					WHEN DATEPART(MM, @STARTDATE ) = 9 THEN 'SEPTEMBER'
					WHEN DATEPART(MM, @STARTDATE ) = 10 THEN 'OCTOBER'
					WHEN DATEPART(MM, @STARTDATE ) = 11 THEN 'NOVEMBER'
					WHEN DATEPART(MM, @STARTDATE ) = 12 THEN 'DECEMBER'
				END
	
	IF ( @PROCESS_TYPE = 'SORT' )
	BEGIN
		SELECT @YIELD = ISNULL(  ( SUM( QTY_OUT ) * 100 / SUM( QTY_IN * GROSS_DIE ) ) ,0.0 ),@QTY_OUT = ISNULL( SUM( QTY_OUT ), 0),@QTY_IN = ISNULL( SUM( QTY_IN * GROSS_DIE ), 0)
		FROM @RESULT_TABLE WHERE DATE_IN >= @STARTDATE AND DATE_OUT <= @ENDDATE
	END
	ELSE
	BEGIN
		SELECT @YIELD = ISNULL(  ( SUM( QTY_OUT ) * 100 / SUM( QTY_IN ) ) ,0.0 ),@QTY_OUT = ISNULL( SUM( QTY_OUT ), 0),@QTY_IN = ISNULL( SUM( QTY_IN ), 0)
		FROM @RESULT_TABLE WHERE DATE_OUT >= @STARTDATE AND DATE_OUT <= @ENDDATE
	END
	INSERT INTO @FINAL_RESULT_TABLE (YIELD, CYTCLETIME_SNO,CYCLETIME_VAR,QTY_IN,QTY_OUT, START_DATE, END_DATE ) VALUES (@YIELD, @CYTCLETIME_SNO,@CYCLETIME_STRING,@QTY_IN,@QTY_OUT, @STARTDATE, @ENDDATE )

	-- FOR CALCULATING THE WORK WEEKS IN THE CURRENT MONTH(considering sunday to saturday as one WW)
	DECLARE @WEEK_START_DATE DATETIME
	DECLARE @WEEK_END_DATE DATETIME
	
	SET @WEEK_START_DATE =  DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
	SET @WEEK_END_DATE =  DATEADD(MS,-2,DATEADD(DAY,1, DATEADD(wk, DATEDIFF(wk, 5, GETDATE()), 5)))
	
	SET @CYTCLETIME_SNO = 5
	WHILE ( @CYTCLETIME_SNO <>0 )
	BEGIN
		SET @CYCLETIME_STRING = N'WW ' + CONVERT(NVARCHAR , @CYTCLETIME_SNO)
		PRINT 'OUT SIDE QUERY STRING : '+@CYCLETIME_STRING
		IF ( @CYTCLETIME_SNO < 5 )
		BEGIN
			SET @WEEK_START_DATE = DATEADD( WW, -1, @WEEK_START_DATE )
			SET @WEEK_END_DATE = DATEADD(MS,-2,DATEADD(DAY,1,DATEADD( D, -1, DATEADD( WW, 1, @WEEK_START_DATE ) )))
		END
		IF ( @PROCESS_TYPE = 'SORT' )
		BEGIN
			SELECT @YIELD = ISNULL(  ( SUM( QTY_OUT ) * 100 / SUM( QTY_IN * GROSS_DIE ) ) ,0.0 ),@QTY_OUT = ISNULL( SUM( QTY_OUT ), 0),@QTY_IN = ISNULL( SUM( QTY_IN * GROSS_DIE ), 0)
			FROM @RESULT_TABLE WHERE DATE_OUT >= CONVERT( VARCHAR, @WEEK_START_DATE, 101) +' 12:00AM' AND DATE_OUT <= CONVERT( VARCHAR, @WEEK_END_DATE, 101) +' 11:59PM'
		END
		ELSE
		BEGIN
			SELECT @YIELD = ISNULL(  ( SUM( QTY_OUT ) * 100 / SUM( QTY_IN ) ) ,0.0 ),@QTY_OUT = ISNULL( SUM( QTY_OUT ), 0),@QTY_IN = ISNULL( SUM( QTY_IN ), 0)
			FROM @RESULT_TABLE WHERE DATE_OUT >= CONVERT( VARCHAR, @WEEK_START_DATE, 101) +' 12:00AM' AND DATE_OUT <= CONVERT( VARCHAR, @WEEK_END_DATE, 101) +' 11:59PM'
		END
	
		INSERT INTO @FINAL_RESULT_TABLE (YIELD, CYTCLETIME_SNO,CYCLETIME_VAR,QTY_IN,QTY_OUT, START_DATE, END_DATE ) VALUES (@YIELD, @CYTCLETIME_SNO, @CYCLETIME_STRING, @QTY_IN, @QTY_OUT, CONVERT( VARCHAR, @WEEK_START_DATE, 101) +' 12:00AM', CONVERT( VARCHAR, @WEEK_END_DATE, 101) +' 11:59PM' )
	
		SET @CYTCLETIME_SNO = @CYTCLETIME_SNO - 1
	END

	SELECT * FROM @FINAL_RESULT_TABLE
END
